Programming Assignment

Total Points: 100 (95 for queries, includes points for establishing the connection; 5 for your feedback)

Extra Credit: 10 pts Nice GUI (see bottom of assignment)

Due: January 25^th, by midnight

For this assignment you will write a self-standing application, which connects to a database and executes a query. Establishing the connection works the same way from any application, so do the work now—you will find it useful for the project.

We recommend completing the assignment in Java or Visual Basic, since these are the languages we can help you with.

Task: Build a simple console application that connects to a database and executes the tasks performed below (there might be computation you need to perform on the data after you have gotten the results of the query).

Steps to follow

This assignment will be completed in your databases, not than the one you used in Homework 1 .

• Last assignment asked you to register ISQL01 with the Enterprise Manager. You need to delete ISQL01 and re-register it. Repeat the registration as last time, only now enter your new login and password.
• Assuming that you have registered ISQL01 successfully, you can just expand the folders (first Microsoft SQL Servers, SQL Server Group, then ISQL01, then Databases). Find your group’s database and double-click it. This will show you all the tables in your database. DO NOT DELETE ANY OF THE SYSTEM TABLES (i.e. not created by you). On the toolbar, click Action->All Tasks-> Import Data. A wizard will start and will ask you for source and later, for destination. The server name is ISQL01 in both cases, the source is my database, login and password are “cse444” “cse444”, the login and password for your group you were given in class. You can copy all of my tables, or just select the ones relevant to this course. When the wizard is done, you need to refresh the view (go one directory up, and when you come back the refresh icon should be on the toolbar).

• Now you should see all the tables that you used for homework 1.

• Create a copy of the tables that you will modify (i.e. Faculty). To do that, repeat the step from above, the source and the destination being your own database. There is a step where the wizard gives you a preview of the table names (looks like [your_group_name].tableName). Make sure to change the tableName to something prefixed with your name, so that people in your group who work later can get a “clean” start. When you do updates, update your copy of the table, rather than the original one. Another way to make this “personal” table is to use the following two SQL statements:

Create Table yourname_Faculty(F_SSN Integer Primary key,

F_Name varchar (10) not null,

F_Mail varchar (10),

F_Room Integer,

F_Salary Integer,

Did char(10) references Department(Did))

Insert into yourname_Faculty

Select * from Faculty

• Before you start coding, you need to create a System DSN (directions). Name your DSN 444 (for uniformity and grading convenience)
• Here is a file to lead you through the steps if you’re a beginner with Java (please check it out before you E-mail with a question)
• Also, see the HOW-TO page for a sample Java file that executes a query the work.
• Here is a list of java classes/interfaces you should pay special attention to:

§ (all in java.sql package): ResultSet, Connection. Notice that the default cursor for ResultSet is Forward_Only

· Please let me know if you want to use VB—I can send you a reference.

What to hand in: Please E-mail your Java, C++ or other program. If there is anything unusual about compiling and/or running your program, please include instructions on how to do it. Your output should be formatted in a way that tells what results are you outputting, so please write a line like: ”Results for question 2 part a: “ and then output the results of the query for that part.

• Schema: The schema is the same as in homework one, but Faculty entity has an added attribute, Did, which specifies which department the faculty belongs to.

Student(S_SSN,S_Name,S_City,S_Age)

Enrollment(CallNo,S_SSN,Grade)

Offering(CourseNo,CallNo,F_SSN)

Course(CourseNo,Title, CrHrs,Did)

Faculty(F_SSN, F_Name,F_Mail,F_Room,F_Salary,Did)

Deaprtment(Did,D_Name,F_SSN,D_Budget)

Queries:95 pts

[25] Write the following query and update:

(a) Find names of all department chairs who teach no course offered by their own department, but teaches one or more courses offered by other departments.

(b) For each such chair, update their salary with a 10% raise in salary.

[35] Write the following query and update:

(a) Find department chairs who earn more than the average salary of the faculty of the department, but their salary is not the maximum among salaries of faculty that belong to that department.

(b) For each department chair, report the amount by which they exceed the average and also report the fraction of the department budget it represents.

(c) Write an update which sets the salary of these chairs to be the maximum salary in the department.

[35] Find faculty who teaches at least as many courses as their department chairs. For each such faculty, provide their name, report their salary, total number of courses offered by this faculty and the total number of courses offered by their department chair (4 columns in output) Order the list by salary. Using cursors, build a console/ASP application so that as you hit "enter",

(a) You view the next faculty in increasing order of salary

(b) You view every other (i.e. every second) faculty in the increasing salary order

(c) You view the previous faculty in the salary order.

Extra Credit[10]: For those of you who are ahead of the game: Write a nice GUI for your application (swing, awt or whatever else you can come up with)

[5 points] Please answer the following questions

1. How long did it take you to complete this assignment?

2. What did you like the best about this assignment?

3. What did you like the least about this assignment?

4. The assignment was too easy OK too hard

5. Instructions were: not enough enough but useless/vague more than I needed